package com.xk.sport.backend.common.util;

import com.google.common.base.Strings;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;

public class XlsUtil {
    /**
     * 解析xls或xlsx文件
     *
     * @param file     文件
     * @param startrow 行开始
     * @param startcol 列开始
     * @param xlsLogic 逻辑
     * @return 错误结果
     */
    public static XlsErrorResult parseXls(MultipartFile file, int startrow, int startcol, XlsLogic xlsLogic) {
        try {
            Workbook wb = WorkbookFactory.create(file.getInputStream());// 读取文件
            Sheet sheet = wb.getSheetAt(0);
            int rowNum = sheet.getLastRowNum() + 1;
            for (int i = startrow; i < rowNum; i++) {// 逐行
                Row row = sheet.getRow(i);
                int cellNum = row.getLastCellNum();
                for (int j = startcol; j < cellNum; j++) {// 逐列
                    Cell cell = row.getCell(Short.parseShort(j + ""));
                    String cellValue = null;// 数据
                    if (null != cell) {
                        switch (cell.getCellTypeEnum()) {
                            case NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellValue = "" + date.getTime();
                                } else {
                                    cellValue = String.valueOf((int) cell.getNumericCellValue());
                                }
                                break;
                            case STRING:
                                cellValue = cell.getStringCellValue();
                                break;
                            case FORMULA:
                                cellValue = cell.getNumericCellValue() + "";
                                break;
                            case BLANK:
                                cellValue = "";
                                break;
                            case BOOLEAN:
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case ERROR:
                                cellValue = String.valueOf(cell.getErrorCellValue());
                                break;
                        }
                    } else {
                        cellValue = "";
                    }
                    XlsErrorResult result = xlsLogic.getCellValue(cellValue, i, j);
                    if (result.hasError()) {
                        result.setI(i);
                        result.setJ(j);
                        return result;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new XlsErrorResult(0, 0, "文件解析异常，原因为文件格式问题或者为文件内容为空");
        }
        return new XlsErrorResult();
    }

    /**
     * 生成xls
     *
     * @param data
     * @return
     */
    public static byte[] createXls(List<List<String>> data) {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("内容");

        int i = 0;
        for (List<String> line : data) {
            XSSFRow row = sheet.createRow(i);
            int j = 0;
            for (String str : line) {
                XSSFCell cell = row.createCell(j);
                cell.setCellValue(str);
                j++;
            }
            i++;
        }

        byte[] bytes = null;
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            wb.write(out);
            bytes = out.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return bytes;
    }

    public interface XlsLogic {
        XlsErrorResult getCellValue(String cellValue, int i, int j);
    }

    public static class XlsErrorResult {
        private int i;// 行
        private int j;// 列
        private String content;// 具体原因

        public XlsErrorResult() {
        }

        public XlsErrorResult(int i, int j, String content) {
            this.i = i;
            this.j = j;
            this.content = content;
        }

        public boolean hasError() {
            return !Strings.isNullOrEmpty(content);
        }

        @Override
        public String toString() {
            int realI = i + 1;
            int realJ = j + 1;
            return "在第" + realI + "行，第" + realJ + "列，问题为：" + content;
        }

        public int getI() {
            return i;
        }

        public void setI(int i) {
            this.i = i;
        }

        public int getJ() {
            return j;
        }

        public void setJ(int j) {
            this.j = j;
        }

        public String getContent() {
            return content;
        }

        public void setContent(String content) {
            this.content = content;
        }
    }
}
